﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text;

namespace StandardDAC.Base {
    /// <summary>
    /// Abstract Controller that does all the work of calling stored procedures and translating data to classes.
    /// </summary>
    public abstract class DBController {
        #region members
        IDBTypeAdapter dbtype;// Connection Type adapter.
        #endregion members
        #region constructor
        /// <summary>
        /// Creates the controller with the given connection type adapter
        /// </summary>
        /// <param name="dbtype"></param>
        public DBController(IDBTypeAdapter dbtype) {
            this.dbtype = dbtype;
        }
        #endregion constructor
        
        #region event handler
        public event PreCommandExecuteEventHandler PreCommandExecute;
        protected virtual void OnPreCommandExecute(PreCommandExecuteEventArgs e) {
            if (PreCommandExecute != null) PreCommandExecute(this, e);
        }
        #endregion

        #region private - add parameters
        /// <summary>
        /// adds all columns as parameters to the given command
        /// </summary>
        /// <param name="fromObject"></param>
        /// <param name="cmd"></param>
        /// <param name="denyType"></param>
        private void AddAll(object fromObject, DbCommand cmd, Type denyType) {
            Dictionary<string, KeyValuePair<int, DBColumn>> parametersToSort = new Dictionary<string, KeyValuePair<int, DBColumn>>();
            foreach (PropertyInfo prop in fromObject.GetType().GetProperties()) {
                if (Attribute.IsDefined(prop, typeof(DBColumn)) && (denyType == null || !Attribute.IsDefined(prop, denyType))) {
                    DBColumn col = (DBColumn)Attribute.GetCustomAttribute(prop, typeof(DBColumn));
                    col.CreateParameter(cmd, prop.GetValue(fromObject, null), true, dbtype);
                    parametersToSort.Add(col.Name, new KeyValuePair<int,DBColumn>(cmd.Parameters.Count - 1,col));
                }
            }
            dbtype.SortParametersIfNeeded(parametersToSort, cmd);
        }
        /// <summary>
        /// Adds primary key columns as parameters to the given command
        /// </summary>
        /// <param name="fromObject"></param>
        /// <param name="cmd"></param>
        private void AddPrimaryKey(object fromObject, DbCommand cmd) {
            Dictionary<string, KeyValuePair<int, DBColumn>> parametersToSort = new Dictionary<string, KeyValuePair<int, DBColumn>>();
            foreach (PropertyInfo info in fromObject.GetType().GetProperties()) {
                if (Attribute.IsDefined(info, typeof(DBColumn))) {
                    DBColumn col = (DBColumn)Attribute.GetCustomAttribute(info, typeof(DBColumn));
                    if (col.IsPrimaryKey) {
                        col.CreateParameter(cmd, info.GetValue(fromObject, null), false, dbtype);
                        parametersToSort.Add(col.Name, new KeyValuePair<int, DBColumn>(cmd.Parameters.Count - 1, col));
                    }
                }
            }
            dbtype.SortParametersIfNeeded(parametersToSort, cmd);
        }
        #endregion private - add parameters
        #region private - execute dataset
        /// <summary>
        /// Gets a dataset for the given query
        /// </summary>
        /// <param name="command">command to execute</param>
        /// <returns></returns>
        private DataSet doExecute(DbCommand command) {
            dbtype.PreExecute(command);
            OnPreCommandExecute(new PreCommandExecuteEventArgs(command));
            DbDataAdapter a = dbtype.NewAdapter(command);
            DataSet ds = new DataSet();
            a.Fill(ds);
            return ds;
        }
        /// <summary>
        /// Executes query and returns dataset. 
        /// </summary>
        /// <param name="connStr">connection string. null if using transaction</param>
        /// <param name="procedureName">stored procedure to use</param>
        /// <param name="values">parameters to pass in. can be null or empty.</param>
        /// <param name="tran">transaction to use. null if not using transaction</param>
        /// <returns></returns>
        private DataSet doGetDataSet(ConnOrTranInfo connStr, string procedureName, DbParameter[] values) {
            using (DbConnection conn = dbtype.NewConnection(connStr)) {
                DbCommand cmd = dbtype.NewCommand(procedureName, conn, connStr);
                cmd.CommandType = CommandType.StoredProcedure;
                if (values != null && values.Length > 0)
                    cmd.Parameters.AddRange(values);
                return doExecute(cmd);
            }            
        }
        #endregion
        #region private - read object
        /// <summary>
        /// Puts data into new object
        /// </summary>
        /// <typeparam name="T">class type of item</typeparam>
        /// <param name="row">data to put in item</param>
        /// <param name="type">class type of item</param>
        /// <returns></returns>
        private T doReadObject<T>(DataRow row, Type type) {
            return doReadObject<T>(row, type, Activator.CreateInstance<T>());
        }
        /// <summary>
        /// Puts data into specified object
        /// </summary>
        /// <typeparam name="T">class type of item</typeparam>
        /// <param name="row">data to put in item</param>
        /// <param name="type">class type of item</param>
        /// <param name="item">item to fill</param>
        /// <returns></returns>
        private T doReadObject<T>(DataRow row, Type type, T item) {
            PropertyInfo[] properties = type.GetProperties();
            Type read = typeof(DenyReadColumn);

            foreach (PropertyInfo property in properties) {
                if (Attribute.IsDefined(property, typeof(DBColumn)) && !Attribute.IsDefined(property, read)) {
                    DBColumn col = (DBColumn)Attribute.GetCustomAttribute(property, typeof(DBColumn));

                    if (row.IsNull(col.Name))
                        continue;

                    Type p = property.PropertyType;
                    if (p.IsGenericType && p.GetGenericTypeDefinition() == typeof(Nullable<>)) {
                        p = p.GetGenericArguments()[0];
                    }

                    object o = row[col.Name];
                    if (p != row[col.Name].GetType()) {
                        o = Convert.ChangeType(row[col.Name], p);
                    }
                    property.SetValue(item, o, null);
                }
            }

            return item;
        }
        #endregion private - read object

        #region public - new transaction
        /// <summary>
        /// Creates a new Transaction
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public DbTransaction NewTransaction(string connStr) {
            return dbtype.NewConnection(new ConnInfo(connStr)).BeginTransaction();
        }
        #endregion public - new transaction
        #region public - fills & execute/fills
        /// <summary>
        /// Populates an object by its Primary Key
        /// </summary>
        /// <typeparam name="T">class type</typeparam>
        /// <param name="connStr">connection string</param>
        /// <param name="procedureName">stored procedure name</param>
        /// <param name="item">item to populate. should have it's primary key set</param>
        public void GetObjectById<T>(ConnOrTranInfo connStr, string procedureName, T item) {
            using (DbConnection conn = dbtype.NewConnection(connStr)) {
                DbCommand cmd = dbtype.NewCommand(procedureName, conn, connStr);
                cmd.CommandType = CommandType.StoredProcedure;

                AddPrimaryKey(item, cmd);

                dbtype.AddOutputCursorIfNeeded(cmd, typeof(T));
                
                DataSet set = doExecute(cmd);
                if (set.Tables[0].Rows.Count > 0)
                    doReadObject<T>(set.Tables[0].Rows[0], typeof(T), item);
            }
        }
        /// <summary>
        /// Populates an object by given parameters. 
        /// </summary>
        /// <typeparam name="T">class type</typeparam>
        /// <param name="connStr">connection string</param>
        /// <param name="procedureName">stored procedure name</param>
        /// <param name="item">item to populate. should have it's primary key set</param>
        /// <param name="values">parameters to pass in</param>
        /// <returns>flag indicating if object could be populated. (ie: a DataRow was returned)</returns>
        public bool GetObjectByParams<T>(ConnOrTranInfo connStr, string procedureName, T item, DbParameter[] values) {
            using (DbConnection conn = dbtype.NewConnection(connStr)) {
                DbCommand cmd = dbtype.NewCommand(procedureName, conn, connStr);
                cmd.CommandType = CommandType.StoredProcedure;

                if (values != null)
                    cmd.Parameters.AddRange(values);

                dbtype.AddOutputCursorIfNeeded(cmd, typeof(T));

                DataSet set = doExecute(cmd);
                if (set.Tables[0].Rows.Count > 0) {
                    doReadObject<T>(set.Tables[0].Rows[0], typeof(T), item);
                    return true;
                } else {
                    return false;
                }
            }
        }
        /// <summary>
        /// Saves an object using the given connection string. (works for both create and update 
        /// procedures. Returns the first output parameter or null if there's no output parameter.)
        /// </summary>
        /// <param name="connStr">connection string</param>
        /// <param name="fromObject">object to save</param>
        /// <param name="procedureName">stored procedure to use</param>
        /// <param name="isCreate">
        ///     Determines the check to apply.
        ///     true: DenyCreateColumn. false: DenyUpdateColumn. null: will not check.
        /// </param>
        /// <returns></returns>
        public object SaveObject(ConnOrTranInfo connStr, object fromObject, string procedureName, bool? isCreate) {
            using (DbConnection conn = dbtype.NewConnection(connStr)) {
                DbCommand cmd = dbtype.NewCommand(procedureName, conn, connStr);
                cmd.CommandType = CommandType.StoredProcedure;

                Type denyType = null;
                if (isCreate != null)
                    denyType = isCreate.Value ? typeof(DenyCreateColumn) : typeof(DenyUpdateColumn);

                AddAll(fromObject, cmd, denyType);

                dbtype.PreExecute(cmd);
                OnPreCommandExecute(new PreCommandExecuteEventArgs(cmd));
                
                cmd.ExecuteNonQuery();
                foreach (DbParameter p in cmd.Parameters) {
                    if (p.Direction == ParameterDirection.Output) {
                        return p.Value;
                    }
                }
                return null;
            }
        }
        /// <summary>
        /// Deletes an object from the database
        /// </summary>
        /// <param name="connStr">connection string</param>
        /// <param name="fromObject">object to delete. primary key must be set</param>
        /// <param name="procedureName">stored procedure to use</param>
        public void DeleteObject(ConnOrTranInfo connStr, object fromObject, string procedureName) {
            using (DbConnection conn = dbtype.NewConnection(connStr)) {
                DbCommand cmd = dbtype.NewCommand(procedureName, conn, connStr);
                cmd.CommandType = CommandType.StoredProcedure;

                AddPrimaryKey(fromObject, cmd);
                dbtype.PreExecute(cmd);
                OnPreCommandExecute(new PreCommandExecuteEventArgs(cmd));
                cmd.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// Runs a stored procedure with no parameters and returns a collection of the given item type.
        /// (Usually this is a Read-All)
        /// </summary>
        /// <typeparam name="T">class type of items to create</typeparam>
        /// <param name="connStr">connection string</param>
        /// <param name="procedureName">stored procedure to use</param>
        /// <returns></returns>
        public List<T> GetCollection<T>(ConnOrTranInfo connStr, string procedureName) {
            return GetCollection<T>(connStr, procedureName, null);

        }
        /// <summary>
        /// Runs a stored procedure with given parameters and returns a collection of the given item type.
        /// </summary>
        /// <typeparam name="T">class type of items to create</typeparam>
        /// <param name="connStr">connection string</param>
        /// <param name="procedureName">stored procedure to use</param>
        /// <param name="values">parameters to pass in</param>
        /// <returns></returns>
        public List<T> GetCollection<T>(ConnOrTranInfo connStr, string procedureName, DbParameter[] values) {
            using (DbConnection conn = dbtype.NewConnection(connStr)) {
                DbCommand cmd = dbtype.NewCommand(procedureName, conn, connStr);
                cmd.CommandType = CommandType.StoredProcedure;
                if (values != null)
                    cmd.Parameters.AddRange(values);

                dbtype.AddOutputCursorIfNeeded(cmd, typeof(T));

                return ReadCollection<T>(doExecute(cmd));
            }
        }

        /// <summary>
        /// Runs a stored procedure with no parameters and fills the collection with the given item type.
        /// (Usually this is a Read-All).
        /// </summary>
        /// <typeparam name="T">class type of items to create</typeparam>
        /// <param name="connStr">connection string</param>
        /// <param name="procedureName">stored procedure to use</param>
        /// <param name="collection">keyed collection, or any collection really. NOT NULL</param>
        public void AddToCollection<T>(ConnOrTranInfo connStr, string procedureName, IList<T> collection) {
            AddToCollection<T>(connStr, procedureName, null, collection);
        }
        /// <summary>
        /// Runs a stored procedure with given parameters and fills the collection with the given item type.
        /// </summary>
        /// <typeparam name="T">class type of items to create</typeparam>
        /// <param name="connStr">connection string</param>
        /// <param name="procedureName">stored procedure to use</param>
        /// <param name="values">parameters to pass in</param>
        /// <param name="collection">keyed collection, or any collection really. NOT NULL</param>
        /// <returns></returns>
        public void AddToCollection<T>(ConnOrTranInfo connStr, string procedureName, DbParameter[] values, IList<T> collection) {
            using (DbConnection conn = dbtype.NewConnection(connStr)) {
                DbCommand cmd = dbtype.NewCommand(procedureName, conn, connStr);
                cmd.CommandType = CommandType.StoredProcedure;
                if (values != null)
                    cmd.Parameters.AddRange(values);

                dbtype.AddOutputCursorIfNeeded(cmd, typeof(T));

                ReadCollection<T>(doExecute(cmd), collection);
            }
        }

        /// <summary>
        /// Returns a populated object from a data-row. (Use this if you have to 
        /// do some custom select that doesn't fit elsewhere. ie: search)
        /// </summary>
        /// <typeparam name="T">class type of item to create</typeparam>
        /// <param name="dataRow">single row of data to use to create item</param>
        /// <returns></returns>
        public T ReadObject<T>(DataRow dataRow) {
            return doReadObject<T>(dataRow, typeof(T), Activator.CreateInstance<T>());
        }
        /// <summary>
        /// Returns a populated collection from a data-set. (Use this if you have to 
        /// do some custom select that doesn't fit elsewhere. ie: search)
        /// </summary>
        /// <typeparam name="T">class type of items to create</typeparam>
        /// <param name="ds">only does the first table in this dataset</param>
        /// <returns></returns>
        public List<T> ReadCollection<T>(DataSet ds) {
            return ReadCollectionEnumerable<T>(ds.Tables[0].Rows);
        }
        /// <summary>
        /// Returns a populated collection from a data-table. (Use this if you have to 
        /// do some custom select that doesn't fit elsewhere. ie: search)
        /// </summary>
        /// <typeparam name="T">class type of items to create</typeparam>
        /// <param name="dt">data-table to use to create items</param>
        /// <returns></returns>
        public List<T> ReadCollection<T>(DataTable dt) {
            return ReadCollectionEnumerable<T>(dt.Rows);
        }
        /// <summary>
        /// Returns a populated collection from a data-view. (Use this if you have to 
        /// do some custom select that doesn't fit elsewhere. ie: search)
        /// </summary>
        /// <typeparam name="T">class type of items to create</typeparam>
        /// <param name="dv">data-view to use to create items</param>
        /// <returns></returns>
        public List<T> ReadCollection<T>(DataView dv) {
            return ReadCollectionEnumerable<T>(dv);
        }
        /// <summary>
        /// Returns a populated collection from a "bunch of rows." (Use this if you have to 
        /// do some custom select that doesn't fit elsewhere. ie: search)
        /// </summary>
        /// <typeparam name="T">class type of items to create</typeparam>
        /// <param name="rows">rows of data to use to create items</param>
        /// <returns></returns>
        public List<T> ReadCollectionEnumerable<T>(IEnumerable rows) {
            List<T> collection = new List<T>();
            ReadCollectionEnumerable<T>(rows, collection);
            return collection;
        }
        /// <summary>
        /// Adds populated items to the collection from a data-set. (Use this if you have to 
        /// do some custom select that doesn't fit elsewhere. ie: search)
        /// </summary>
        /// <typeparam name="T">class type of items to create</typeparam>
        /// <param name="ds">only does the first table in this dataset</param>
        /// <param name="collection">keyed collection, or any collection really. NOT NULL</param>
        /// <returns></returns>
        public void ReadCollection<T>(DataSet ds, IList<T> collection) {
            ReadCollectionEnumerable<T>(ds.Tables[0].Rows, collection);
        }
        /// <summary>
        /// Adds populated items to the collection from a data-table. (Use this if you have to 
        /// do some custom select that doesn't fit elsewhere. ie: search)
        /// </summary>
        /// <typeparam name="T">class type of items to create</typeparam>
        /// <param name="dt">data-table to use to create items</param>
        /// <param name="collection">keyed collection, or any collection really. NOT NULL</param>
        /// <returns></returns>
        public void ReadCollection<T>(DataTable dt, IList<T> collection) {
            ReadCollectionEnumerable<T>(dt.Rows, collection);
        }
        /// <summary>
        /// Adds populated items to the collection from a data-view. (Use this if you have to 
        /// do some custom select that doesn't fit elsewhere. ie: search)
        /// </summary>
        /// <typeparam name="T">class type of items to create</typeparam>
        /// <param name="dv">data-view to use to create items</param>
        /// <param name="collection">keyed collection, or any collection really. NOT NULL</param>
        /// <returns></returns>
        public void ReadCollection<T>(DataView dv, IList<T> collection) {
            ReadCollectionEnumerable<T>(dv, collection);
        }
        /// <summary>
        /// Adds populated items to the collection from a "bunch of rows." (Use this if you have to 
        /// do some custom select that doesn't fit elsewhere. ie: search)
        /// </summary>
        /// <typeparam name="T">class type of items to create</typeparam>
        /// <param name="rows">rows of data to use to create items</param>
        /// <param name="collection">keyed collection, or any collection really. NOT NULL</param>
        /// <returns></returns>
        public void ReadCollectionEnumerable<T>(IEnumerable rows, IList<T> collection) {
            Type type = typeof(T);
            foreach (DataRow currentRow in rows) {
                collection.Add(doReadObject<T>(currentRow, type));
            }
        }
        #endregion public - fills & execute/fills
        #region public - executes
        /// <summary>
        /// Executes procedure with parameters. No Result set.
        /// </summary>
        /// <param name="connStr">connection string</param>
        /// <param name="procedureName">stored procedure to use</param>
        /// <param name="values">parameters to pass in. can be null or empty.</param>
        public void Execute(ConnOrTranInfo connStr, string procedureName, DbParameter[] values) {
            doGetDataSet(connStr, procedureName, values);
        }
        /// <summary>
        /// Executes query and returns datatable
        /// </summary>
        /// <param name="connStr">connection string</param>
        /// <param name="procedureName">stored procedure to use</param>
        /// <param name="values">parameters to pass in. can be null or empty.</param>
        /// <returns></returns>
        public DataTable GetTable(ConnOrTranInfo connStr, string procedureName, DbParameter[] values) {
            return doGetDataSet(connStr, procedureName, values).Tables[0];
        }
        /// <summary>
        /// Executes query and returns first datarow
        /// </summary>
        /// <param name="connStr">connection string</param>
        /// <param name="procedureName">stored procedure to use</param>
        /// <param name="values">parameters to pass in. can be null or empty.</param>
        /// <returns></returns>
        public DataRow GetRow(ConnOrTranInfo connStr, string procedureName, DbParameter[] values) {
            DataTable dt = doGetDataSet(connStr, procedureName, values).Tables[0];
            return dt.Rows.Count > 0 ? dt.Rows[0] : null;
        }
        /// <summary>
        /// Executes query and returns dataset
        /// </summary>
        /// <param name="connStr">connection string</param>
        /// <param name="procedureName">stored procedure to use</param>
        /// <param name="values">parameters to pass in. can be null or empty.</param>
        /// <returns></returns>
        public DataSet GetDataSet(ConnOrTranInfo connStr, string procedureName, DbParameter[] values) {
            return doGetDataSet(connStr, procedureName, values);
        }
        #endregion
        #region public - inline-sql
        /// <summary>
        /// Executes query. Will return datasets if available.
        /// </summary>
        /// <param name="connStr">connection string</param>
        /// <param name="sql">sql to execute</param>
        /// <param name="values">parameters to pass in with the query. can be null/blank</param>
        /// <returns></returns>
        public DataSet InlineSql_Execute(ConnOrTranInfo connStr, string sql, DbParameter[] values) {
            using (DbConnection conn = dbtype.NewConnection(connStr)) {
                DbCommand cmd = dbtype.NewCommand(sql, conn, connStr);
                cmd.CommandType = CommandType.Text;
                if (values != null && values.Length > 0)
                    cmd.Parameters.AddRange(values);
                return doExecute(cmd);
            }
        }
        #endregion public - inline-sql
        #region public - Table_Read for Ole and ODBC connections: allParametersOfReadProcInOrder
        public void GetObjectById<T>(List<string> allParametersOfReadProcInOrder, ConnOrTranInfo connStr, string procedureName, T item) {
            using (DbConnection conn = dbtype.NewConnection(connStr)) {
                DbCommand cmd = dbtype.NewCommand(procedureName, conn, connStr);
                cmd.CommandType = CommandType.StoredProcedure;

                AddPrimaryKey(item, cmd);

                FixParameters(allParametersOfReadProcInOrder, cmd);

                DataSet set = doExecute(cmd);
                if (set.Tables[0].Rows.Count > 0)
                    doReadObject<T>(set.Tables[0].Rows[0], typeof(T), item);
            }
        }

        public List<T> GetCollection<T>(List<string> allParametersOfReadProcInOrder, ConnOrTranInfo connStr, string procedureName) {
            List<T> collection = new List<T>();
            AddToCollection(allParametersOfReadProcInOrder, connStr, procedureName, null, collection);
            return collection;
        }

        public List<T> GetCollection<T>(List<string> allParametersOfReadProcInOrder, ConnOrTranInfo connStr, string procedureName, DbParameter[] values) {
            List<T> collection = new List<T>();
            AddToCollection(allParametersOfReadProcInOrder, connStr, procedureName, values, collection);
            return collection;
        }

        public void AddToCollection<T>(List<string> allParametersOfReadProcInOrder, ConnOrTranInfo connStr, string procedureName, IList<T> collection) {
            AddToCollection(allParametersOfReadProcInOrder, connStr, procedureName, null, collection);
        }

        public void AddToCollection<T>(List<string> allParametersOfReadProcInOrder, ConnOrTranInfo connStr, string procedureName, DbParameter[] values, IList<T> collection) {
            using (DbConnection conn = dbtype.NewConnection(connStr)) {
                DbCommand cmd = dbtype.NewCommand(procedureName, conn, connStr);
                cmd.CommandType = CommandType.StoredProcedure;
                if (values != null)
                    cmd.Parameters.AddRange(values);

                FixParameters(allParametersOfReadProcInOrder, cmd);

                ReadCollection<T>(doExecute(cmd), collection);
            }
        }

        private void FixParameters(List<string> allParametersOfReadProcInOrder, DbCommand cmd) {
            DbParameter[] ary = new DbParameter[cmd.Parameters.Count];
            cmd.Parameters.CopyTo(ary, 0);
            cmd.Parameters.Clear();
            for (int i = 0; i < allParametersOfReadProcInOrder.Count; i++) {
                string current = dbtype.ConvertNameToParam(allParametersOfReadProcInOrder[i]);
                bool found = false;
                foreach (DbParameter p in ary) {
                    if (current == p.ParameterName) {
                        found = true;
                        dbtype.AddExistingParameter(p, cmd);
                        break;
                    }
                }

                if (!found) {
                    dbtype.AddNewNullParameter(allParametersOfReadProcInOrder[i], cmd);
                }
            }
        }
        #endregion public - Table_Read for Ole and ODBC connections: allParametersOfReadProcInOrder
    }
}
